package org.office.api.convertor;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.Node;
import org.dom4j.io.SAXReader;
import org.office.api.model.ModelAttribute;
import org.office.api.model.ModelClass;
import org.office.api.utils.Dom4jUtil;
import org.office.api.utils.ExcelUtil;

/** 
 * <pre>
 * 转换器抽象类
 * </pre>
 * 
 * <pre> 
 * 构建组：office-api
 * 作者：eddy
 * 邮箱：1546077710@qq.com
 * 日期：2016年11月2日-下午3:55:32
 * 版权：
 * </pre>
 */
public abstract class AbstractConvertor implements IConvertor {

	public abstract String type();

	public Document configDoc(String confPath) throws Exception {
		SAXReader reader = new SAXReader();
		try {
			Document doc = reader.read(new File(confPath));
			return doc;
		} catch (Exception e) {
			throw new Exception("解析[" + confPath + "]文件错误", e);
		}
	}

	public abstract List<ModelClass> readData(String fileName, InputStream stream, List<ModelClass> modelClassList)throws IOException;

	public abstract List<ModelClass> readData(String fileName, InputStream stream, ModelClass modelClass)throws IOException;
	
	public abstract List<ModelClass> readData(String fileName, String filePath, ModelClass modelClass)throws IOException;
	
	public List<ModelClass> modelClassList(String confPath) throws Exception{
		List<ModelClass> modelClassList = new ArrayList<ModelClass>();
		Document configDoc = null;
		try {
			configDoc = configDoc(confPath);
		} catch (Exception e) {
			throw e;
		}
		
		List<Node> modeles = configDoc.selectNodes("/root/modeles/class");
		if(null == modeles || modeles.size() < 1) throw new Exception("无效配置，没有/root/modeles/class数据！");
		
		for (Node node : modeles) {
			Element modelElement = (Element)node;
			ModelClass modelClass = new ModelClass();
			
			modelClass.setName(Dom4jUtil.getChildString(modelElement, "name", false));
			modelClass.setTable(Dom4jUtil.getChildString(modelElement, "table", false));
			modelClass.setSchemas(Dom4jUtil.getChildString(modelElement, "schemas", true));
			modelClass.setSn(Dom4jUtil.getChildInteger(modelElement, "sn", true));
			modelClass.setIndex(Dom4jUtil.getChildInteger(modelElement, "index", true));
			
			List<ModelAttribute> attributes = new ArrayList<ModelAttribute>();
			List<Element> attrElements = modelElement.elements("attribute");
			for (Element attrElement : attrElements) {
				ModelAttribute attribute = new ModelAttribute();
				Boolean pk = Dom4jUtil.getChildBoolean( attrElement, "pk", true);
				if(null != pk) attribute.setPk(pk);
				Boolean auto = Dom4jUtil.getChildBoolean( attrElement, "auto", true);
				if(null != pk) attribute.setAuto(auto);
				attribute.setName(Dom4jUtil.getChildString( attrElement, "name", false));
				attribute.setField(Dom4jUtil.getChildString( attrElement, "field", false));
				Integer index = Dom4jUtil.getChildInteger( attrElement, "index", true);
				if(null != index) attribute.setIndex(index);
				attribute.setBean(Dom4jUtil.getChildString( attrElement, "bean", false));
				attributes.add(attribute);
			}
			modelClass.setAttributes(attributes);
			modelClassList.add(modelClass);
		}
		
		return modelClassList;
	}
	
	public List<ModelClass> convert(String confPath, String filePath) throws Exception {
		List<ModelClass> modelClassList = modelClassList(confPath);
		InputStream stream = new FileInputStream(filePath);
		List<ModelClass> modelValueList = readData(filePath, stream, modelClassList);
		return modelValueList;
	}

	public List<ModelClass> convert(String confPath, String fileName, InputStream stream) throws Exception{
		List<ModelClass> modelClassList = modelClassList(confPath);
		List<ModelClass> modelValueList = readData(fileName, stream, modelClassList);
		return modelValueList;
	}

	public String convert2Sql(ModelClass modelClass){
		StringBuilder sqlBuilder = new StringBuilder("insert into ");
		sqlBuilder.append(modelClass.getTable().toLowerCase());
		sqlBuilder.append(" (");
		StringBuilder sqlValueBuilder = new StringBuilder("values(");
		for(ModelAttribute attribute : modelClass.getAttributes()){
			sqlBuilder.append(attribute.getField());
			sqlBuilder.append(",");
			sqlValueBuilder.append("?,");
		}
		if(null != modelClass.getAttributes() && 0 != modelClass.getAttributes().size()){
			sqlBuilder.setLength(sqlBuilder.length() - 1);
			sqlValueBuilder.setLength(sqlValueBuilder.length() - 1);
		}
		sqlBuilder.append(") ");
		sqlValueBuilder.append(")");
		sqlBuilder.append(sqlValueBuilder);
		
		return sqlBuilder.toString();
	}
	
	public List<Map<String, Object>> convert2SqlParamValue(List<ModelClass> modelClass){
		List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
		
		for(ModelClass model : modelClass){
			Map<String, Object> paramMap = new HashMap<String, Object>();
			for(ModelAttribute attribute : model.getAttributes()){
				paramMap.put(attribute.getField().toLowerCase(), attribute.getValue());
			}
			results.add(paramMap);
		}
		
		return results;
	}
	
	/**
	 * 读取Excel 2003
	 *
	 * @param stream
	 * @param modelClassList
	 * @return
	 * @throws IOException 
	 */
	public static List<ModelClass> readXls(HSSFSheet hssfSheet, ModelClass model) throws IOException {
		List<ModelClass> results = new ArrayList<ModelClass>();
		
		if (null == hssfSheet) {
			return results;
		}
		
		// Read the Row
		HSSFRow hssfRow = null;
		HSSFCell hssfCell = null;
		int index = model.getIndex();
		for (int rowNum = index; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
			hssfRow = hssfSheet.getRow(rowNum);
			if (null == hssfRow) continue;
			
			ModelClass result = model.clone();
			for(ModelAttribute attribute : result.getAttributes()){
				int indexX = Integer.valueOf(attribute.getIndex());
				hssfCell = hssfRow.getCell(indexX);
				String value = ExcelUtil.getValue(hssfCell, attribute.getBean());
				attribute.setValue(value);
			}
			results.add(result);
		}
		
		return results;
	}
	
	/**
	 * 读取Excel 2007
	 *
	 * @param stream
	 * @param modelClassList
	 * @return
	 * @throws IOException 
	 */
	public static List<ModelClass> readXlsx(XSSFSheet xssfSheet, ModelClass model) throws IOException {
		List<ModelClass> results = new ArrayList<ModelClass>();
		
		if (null == xssfSheet) {
			return results;
		}
		
		// Read the Row
		XSSFRow xssfRow = null;
		XSSFCell xssfCell = null;
		int index = model.getIndex();
		for (int rowNum = index; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
			xssfRow = xssfSheet.getRow(rowNum);
			if (null == xssfRow) continue;
			
			ModelClass result = model.clone();
			for(ModelAttribute attribute : result.getAttributes()){
				int indexX = Integer.valueOf(attribute.getIndex());
				xssfCell = xssfRow.getCell(indexX);
				String value = ExcelUtil.getValue(xssfCell, attribute.getBean());
				attribute.setValue(value);
			}
			results.add(result);
		}
		
		return results;
	}
	
}
